package org.jctc.els.modules.biz.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PathVariable;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelImportUtil {
	public static List<Map<Integer,String>> importFiles(@PathVariable("files") File files, @PathVariable("cellLength") Integer cellLength) throws Exception {
		Workbook wookbook = null;
		FileInputStream fis = null;
		int cellType = CellType.STRING.getCode();
		try {
			fis = new FileInputStream(files);
			//用HSSF来处理，有异常即为xlsx格式，用XSSF处理
			wookbook = new HSSFWorkbook(fis);//得到工作簿
			cellType = CellType.STRING.getCode();
		} catch (Exception e) {
			try {
				fis = new FileInputStream(files);//这里不创建输入流就会报错stream close
				wookbook = new XSSFWorkbook(fis);
				cellType = 1;
			} catch (Exception e1) {
				//返回文件格式错误异常
				throw new Exception("This file is not in excel format");
			}//得到工作簿
		} finally {
			fis.close();
		}
		//得到一个工作表
		Sheet sheet = wookbook.getSheetAt(0);
		//获得数据的总行数
		int totalRowNum = sheet.getLastRowNum();
		//要获得属性
		List<Map<Integer,String>> list = new ArrayList<Map<Integer,String>>();
		Map<Integer,String> map = null;
		//获得所有数据
		//从第x行开始获取
		for(int x = 1 ; x <= totalRowNum ; x++){
			map = new HashMap<Integer,String>();
			//获得第i行对象
			Row row = sheet.getRow(x);
			//如果一行里的所有单元格都为空则不放进list里面
			int a = 0;
			for(int y=0;y<cellLength;y++){
				Cell cell = row.getCell(y);
				if(cell == null){
					map.put(y,"");
				}else{
					/*//POI对单元格日期处理很弱，没有针对的类型，日期类型取出来的也是一个double值，所以同样作为数值类型
                    //解决日期2006/11/02格式读入后出错的问题，POI读取后变成“02-十一月-2006”格式   62-71*/
					if(cell.toString().contains("-") && checkDate(cell.toString())){
						String ans = "";
						try {
							ans = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
							System.out.println("ans"+ans);
						} catch (Exception e) {
							ans = cell.toString();
							System.out.println("ansCatch"+ ans);
						}
						map.put(y, ans);
						continue;
					}
					cell.setCellType(CellType.STRING);
					map.put(y, cell.getStringCellValue());
				}
				if(map.get(y)==null||"".equals(map.get(y))){
					a++;
				}
			}
			if(a!=cellLength){
				list.add(map);
			}
		}
		return list;
	}

	/**
	 * 判断是否是“02-十一月-2006”格式的日期类型
	 */
	private static boolean checkDate(String str){
		String[] dataArr =str.split("-");
		try {
			if(dataArr.length == 3){
				int x = Integer.parseInt(dataArr[0]);
				String y = dataArr[1];
				int z = Integer.parseInt(dataArr[2]);
				if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
					return true;
				}
			}
		} catch (Exception e) {
			return false;
		}
		return false;
	}

}
